USAGE = """
    mysql -upygame -ppygame pygame < phils_dump.sql
    
    mysqldump -upygame -ppygame --no-create-info pygame > inserts.sql
    
    python db.py drop
    
    mysql -upygame -ppygame pygame < inserts.sql
    
    python db.py alter
    
    ipython db.py


Then to do a query...
    [n.title for n in Session.query(News)]

    [n.title for n in Session.query(News)]

sqlalchemy tutorial here...
http://www.sqlalchemy.org/docs/05/ormtutorial.html
"""



#################################### IMPORTS ###################################

from sqlalchemy import ( Binary, Boolean, Date, DateTime, Float, Integer,
                         Numeric, String, Text, Time, Unicode, UnicodeText, 
                         and_, or_, asc, desc, distinct,
                         Table, Column, ForeignKey, DefaultClause,
                         create_engine, MetaData )

from sqlalchemy.orm import ( relation, mapper, scoped_session,
                             deferred, lazyload, object_mapper,
                             sessionmaker, dynamic_loader, class_mapper )

################################################################################

"""

+------------------+
| Tables_in_pygame |
+------------------+
| docscomment      |
| groups           |
| modules          |
| news             |
| node             |
| project          |
| projectcomment   |
| release          |
| skin             |
| tags             |
| users            |
| users_groups     |
| wiki             |
| wikicomment      |
+------------------+
    
"""

################################################################################


############################# CLASSICAL SQL ALCHEMY ############################

# Normally would import this stuff from framework where cherry py will configure
# it based on environment
engine = create_engine('mysql://pygame:pygame@localhost/pygame?charset=utf8')

metadata = MetaData(bind=engine)

# Proxy so can use each threads own session with just one Session reference
Session = scoped_session(sessionmaker(bind=engine))

# Handy alias
Q = Session.query

# Using this, items instantiated will auto flush and automatically be added to
# the session.  Not saved until you actually call Session.commit() Endows each
# object with a query method `Object.query`

mapper = Session.mapper

################################################################################

# Create a new object 

projectcomment_table = Table(u'projectcomment', metadata,

    Column(u'id', Integer(display_width=11), primary_key=True,  nullable=False), 
    Column(u'project_id', Integer(display_width=11), ForeignKey("project.id")), 
    Column(u'users_id', Integer(display_width=11), ForeignKey("users.id")), 
    Column(u'datetimeon', DateTime(timezone=False)), 
    Column(u'content', UnicodeText()), 
    Column(u'rating', Integer(display_width=11))
)

tags_table = Table(u'tags', metadata,
    Column(u'id', Integer(display_width=11), primary_key=True,  nullable=False),
    Column(u'project_id', Integer(display_width=11), ForeignKey("project.id")), 
    Column(u'value', Unicode(length=32))
)

project_table = Table(u'project', metadata,

    Column(u'id', Integer(display_width=11), primary_key=True, nullable=False), 
    Column(u'users_id', Integer(display_width=11), ForeignKey("users.id")), 
    Column(u'node_id', Integer(display_width=11), ForeignKey("node.id")), 
    Column(u'title', Unicode(length=80)), 
    Column(u'summary', UnicodeText()), 
    Column(u'description', UnicodeText()), 
    Column(u'uri', Unicode(length=255)), 
    Column(u'datetimeon', DateTime(timezone=False)), 
    Column(u'image', Unicode(length=80))

)

skin_table = Table(u'skin', metadata,

    Column(u'id', Integer(display_width=11), primary_key=True, nullable=False), 
    Column(u'title', Unicode(length=80)), 
    Column(u'fname', Unicode(length=80)), 
    Column(u'orders', Integer(display_width=11))
)

docscomment_table = Table(u'docscomment', metadata,

    Column(u'id', Integer(display_width=11), primary_key=True, nullable=False), 
    Column(u'link', Unicode(length=255)), 
    Column(u'users_id', Integer(display_width=11), ForeignKey("users.id")), 
    Column(u'datetimeon', DateTime(timezone=False)), 
    Column(u'content', UnicodeText())
)

news_table = Table(u'news', metadata,

    Column(u'id', Integer(display_width=11), primary_key=True, nullable=False), 
    Column(u'title', Unicode(length=80)), 
    Column(u'description', UnicodeText()), 
    Column(u'datetimeon', DateTime(timezone=False)), 
    Column(u'submit_users_id', Integer(display_width=11), ForeignKey("users.id"))
)

users_table = Table('users', metadata,

    Column(u'id', Integer(display_width=11), primary_key=True, nullable=False), 
    Column(u'name', Unicode(length=80)), 
    Column(u'email', Unicode(length=80)), 
    Column(u'passwd', Unicode(length=80)), 
    Column(u'title', Unicode(length=80)), 
    Column(u'disabled', Integer(display_width=11), 
                        server_default=DefaultClause('0', for_update=False)), 
    Column(u'super', Integer(display_width=11), 
                     server_default=DefaultClause('0', for_update=False))
)

users_groups_table = Table(u'users_groups', metadata,

    Column(u'users_id', Integer(display_width=11),  ForeignKey("users.id") ), 
    Column(u'groups_id', Integer(display_width=11), ForeignKey("groups.id") )
)

groups_table = Table(u'groups', metadata,

    Column(u'id', Integer(display_width=11), primary_key=True, nullable=False), 
    Column(u'name', Unicode(length=80)), 
    Column(u'title', Unicode(length=80)), 
    Column(u'orders', Integer(display_width=11)) 
)

node_table = Table(u'node', metadata,

    Column(u'id', Integer(display_width=11), primary_key=True, nullable=False), 
    Column(u'name', Unicode(length=255)), 
    Column(u'title', Unicode(length=255)), 
    Column(u'keywords', Unicode(length=255)), 
    Column(u'type', Unicode(length=255)), 
    Column(u'content', UnicodeText()), 
    Column(u'summary', UnicodeText()), 
    Column(u'orders', Integer(display_width=11), nullable=False, 
                      server_default=DefaultClause('0', for_update=False)), 

    Column(u'parentid', Integer(display_width=11)),  # TODO

    Column(u'link', Unicode(length=255)), 
    Column(u'hidden', Integer(display_width=11), 
                      server_default=DefaultClause('0', for_update=False)), 
    Column(u'target', Unicode(length=80)), 
    Column(u'custom', Binary()), 
    Column(u'skin_id', Integer(display_width=11), ForeignKey("skin.id")), 
    Column(u'uri', Unicode(length=255)), 
    Column(u'users_id', Integer(display_width=11), ForeignKey("users.id")), 
    Column(u'groups_id', Integer(display_width=11), ForeignKey("groups.id")), 
    Column(u'mods', Integer(display_width=11)), 
    Column(u'folderid', Integer(display_width=11)), 
    Column(u'folder', Integer(display_width=11), 
                      server_default=DefaultClause('0',for_update=False)), 
    
    Column(u'modules_id', Integer(display_width=11), ForeignKey("modules.id")), 
    Column(u'image', Unicode(length=255))
)

release_table = Table(u'release', metadata,

    Column(u'id', Integer(display_width=11), primary_key=True, nullable=False), 
    Column(u'project_id', Integer(display_width=11), ForeignKey("project.id")), 
    Column(u'datetimeon', DateTime(timezone=False)), 
    Column(u'description', UnicodeText()), 
    Column(u'srcuri', Unicode(length=255)), 
    Column(u'winuri', Unicode(length=255)), 
    Column(u'macuri', Unicode(length=255)), 
    Column(u'title', Unicode(length=80))
)

modules_table = Table(u'modules', metadata,

    Column(u'id', Integer(display_width=11), primary_key=True, nullable=False), 
    Column(u'name', Unicode(length=80)), 
    Column(u'title', Unicode(length=80)), 
    Column(u'orders', Integer(display_width=11))
)

wiki_table = Table(u'wiki', metadata,

    Column(u'id', Integer(display_width=11), primary_key=True, nullable=False), 
    Column(u'link', Unicode(length=255)), 
    Column(u'summary', UnicodeText()), 
    Column(u'content', UnicodeText()), 
    Column(u'datetimeon', DateTime(timezone=False)), 
    Column(u'fname', Unicode(length=255)), 
    Column(u'changes', Unicode(length=255)), 
    Column(u'users_id', Integer(display_width=11), ForeignKey("users.id")), 
    Column(u'latest', Integer(display_width=11)), 
    Column(u'name', Unicode(length=255)), 
    Column(u'title', Unicode(length=255)), 
    Column(u'parent', Unicode(length=255)), 
    Column(u'keywords', Unicode(length=255))   
)

wikicomment_table = Table(u'wikicomment', metadata,

    Column(u'id', Integer(display_width=11), primary_key=True, nullable=False), 
    Column(u'link', Unicode(length=255)), 
    Column(u'users_id', Integer(display_width=11), ForeignKey("users.id") ), 
    Column(u'datetimeon', DateTime(timezone=False)), 
    Column(u'content', UnicodeText())
)                    


################################## BASE CLASS ##################################

# Usually inherit base class with set of common helpers from framework

def set_attrs(self, *args, **kw):
    if args: kw=args[0]
    for k in self._sa_class_manager.keys():
        if k == 'id': continue
        if k in kw:
            setattr(self, k, kw[k])

class Base(object):
    repr_keys = ('id', 'name', 'title')

    def __unicode__(self):
        attrs = ', '.join (
                        [ "%s=%r" % (k, getattr(self, k)) 
                         for k in self.repr_keys
                         if hasattr(self, k) ] )

        return u"%s(%s)" % (type(self).__name__, attrs)

    @property
    def as_dict(self):
        " Returns a flat dict (no relations) representing the underlying row "
        return dict (
            (c.name, getattr(self, c.name)) for c in object_mapper(self).c 
        )

    def __repr__(self):
        return unicode(self).encode('utf8')

    __init__ = set_attrs                                                                                                                                                            

#################################### OBJECTS ###################################

class DocComment(Base): pass

class Group(Base): pass
class User(Base): pass

class News(Base): pass

class Module(Base): pass
class Node(Base): pass

class Release(Base): pass
class Project(Base): pass
class ProjectComment(Base): pass
class Tag(Base): pass

class Skin(Base): pass

class Wiki(Base): pass
class WikiComment(Base): pass

################################# DATA MAPPINGS ################################

# http://www.sqlalchemy.org/docs/05/mappers.html#relation-configuration


# Some relations aren't set up
mapper(DocComment, docscomment_table)
mapper(Module, modules_table)
mapper(News, news_table)
mapper(Node, node_table)

mapper(Skin, skin_table)

# Wiki
mapper(WikiComment, wikicomment_table)
mapper(Wiki, wiki_table, properties={
    # 'comments': relation(WikiComment) // join?
})

# Projects
mapper(Tag, tags_table)   #Tag needs a primary key

mapper(Release, release_table)

mapper(ProjectComment, projectcomment_table, properties={
    'user' : relation(User, uselist=False, lazy=True)
})
mapper(Project, project_table, properties={
    'comments': relation(ProjectComment),
    'tags'    : dynamic_loader(Tag),
    'releases': dynamic_loader(Release)
})


# Users
mapper(User, users_table, properties={
    'projects': dynamic_loader(Project),
    'groups'  : dynamic_loader(Group, secondary=users_groups_table),
})

# Dynamic loader will not hit the database until it actually needs it
#  for user in group.users: do_stuff(user)
#  It is actually a Query object
#  for user in group.users.filter(User.name.like('%s%')): do_stuff(user)

mapper(Group, groups_table, properties= {
    'users' : dynamic_loader( User, secondary = users_groups_table )
})

################################################################################

def drop_create():
    # Set database to use utf8
    utf8 = ( "alter database pygame collate utf8_general_ci;",
             "alter database pygame default character set utf8;" )

    map(engine.execute, utf8)

    metadata.drop_all()
    metadata.create_all()

    insert_preparations = (
        # Remove the id field temporarily so insert sql has right column length
        "ALTER TABLE `tags` DROP `id`",
    )

    map(engine.execute, insert_preparations)

    print "\n\nIMPORT INSERT ONLY SQL NOW"

def alter():
    alterations = (
        "ALTER TABLE `tags` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY;",
    )

    map(engine.execute, alterations)

if __name__ == '__main__':
    import sys
    engine.echo = True

    if 'drop' in sys.argv:  drop_create()
    if 'alter' in sys.argv: alter()
    if 'help' in ''.join(sys.argv[1:]): print USAGE
